﻿
using Cassandra;
using IDAdmin.Lib.DataLayer;
using IDAdmin.Lib.UI;
using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.UI;
using System.Web.UI.WebControls;
using MoreLinq;
using Renci.SshNet;
using System.Text;
using System.IO;
using System.Drawing;
using System.Globalization;
using System.Threading;
namespace IDAdmin.Pages
{
    class ActiveCode
    {
        public string Code { get; set; }
        public string UseTime { get; set; }

        public string Name { get; set; }
    }
    class On100Rechard
    {
        public string account { get; set; }
        public DateTime datetime { get; set; }
    }

    class GoodEvilCard
    {
        public string datetime { get; set; }
        public int cardNum { get; set; }
    }

    class CardHero
    {
        public DateTime datetime { get; set; }
        public long ticks { get; set; }
        public string objname { get; set; }
        public string baseid { get; set; }
        public string change { get; set; }
        public string shop { get; set; }
        public string characterName { get; set; }
        public string action { get; set; }
        public string ZoneName { get; set; }
    }

    class AbadonFillCard
    {
        public string ID { get; set; }
        public string FillCard { get; set; }
        public string Abadon { get; set; }
    }
    public partial class TempFunc : System.Web.UI.Page
    {
        List<Server> serverList;

        public const string hostName = "222.255.177.27";
        public const int port = 9823;

        protected void Page_Load(object sender, EventArgs e)
        {
            serverList = WebDB_N.ListServer();
        }
        public Table getOn100Rechard(List<DateTime> dateTimeList)
        {
            List<CharBase> cbList = new List<CharBase>();
            List<CharBase> cbList2 = new List<CharBase>();
            List<On100Rechard> On100RechardList = new List<On100Rechard>();
            foreach (Server server in serverList)
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    string casCmdText = "SELECT charid,accid,level  FROM " + "charbase" + server.tablezone;
                    try
                    {
                        RowSet rowSet = session.Execute(casCmdText);
                        foreach (Row r in rowSet)
                        {
                            CharBase cb = new CharBase();
                            cb.CharId = r["charid"].ToString();
                            cb.Level = r["level"].ToString();
                            cb.AccId = r["accid"].ToString();
                            cbList.Add(cb);
                        }
                    }
                    catch (Exception ex)
                    { }
                }

                foreach (DateTime dt in dateTimeList)
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand cmd = new SqlCommand();
                        string cmdText = "Select charid,max(level) from level{0} as le where le.level>=100 and [datetime] between '{1}' and '{2}' group by le.charid";
                        cmdText = string.Format(cmdText, server.tablezone, server.created, dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        cmd.Connection = conn;
                        cmd.CommandText = cmdText;
                        SqlDataReader dataReader = cmd.ExecuteReader();

                        while (dataReader.Read())
                        {
                            var cbTemp = cbList.Where(w => w.CharId.Equals(dataReader.GetValue(0).ToString()));
                            CharBase cb = new CharBase();
                            if (cbTemp != null)
                            {
                                cb = cbList.Where(w => w.CharId.Equals(dataReader.GetValue(0).ToString())).ElementAt(0);
                            }
                            if (string.IsNullOrEmpty(cb.AccId) == false)
                            {
                                string ip = "222.255.177.23";
                                string port = "8080";
                                string url = "http://{0}:{1}/getinfo?uid={2}&all=";
                                url = string.Format(url, ip, port, cb.AccId);
                                string result = HttpHelper.HttpSocket(url, 26);
                                if (string.IsNullOrEmpty(result) == false && result.Contains("account"))
                                {
                                    string gosuAccount = result.Substring(result.IndexOf("account") + 8, result.Substring(result.IndexOf("account") + 8).IndexOf("type") - 1);
                                    SqlCommand filledCardCmd = new SqlCommand();
                                    string filledCardCmdText = "Select account from filledcard where account='{0}' and filltime between '{1}' abd '{2}'";
                                    filledCardCmdText = string.Format(cmdText, gosuAccount, dt.ToString("yyyy-MM-dd") + "00:00:00", dt.ToString("yyyy-MM-dd") + "23:59:59");
                                    filledCardCmd.Connection = conn;
                                    filledCardCmd.CommandText = filledCardCmdText;
                                    SqlDataReader fcDataReader = filledCardCmd.ExecuteReader();
                                    if (fcDataReader.Read())
                                    {
                                        On100Rechard o100r = new On100Rechard();
                                        o100r.account = gosuAccount;
                                        o100r.datetime = dt;
                                        On100RechardList.Add(o100r);
                                    }
                                    fcDataReader.Close();
                                }
                            }
                        }
                        dataReader.Close();
                    }
                }
            }
            On100RechardList = On100RechardList.DistinctBy(d => d.account).ToList();
            var rl = (from tb in On100RechardList group tb by new { tb.datetime } into grp select new { DateTime = grp.Key, AccountNum = grp.Count() });
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Thời gian",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Số tài khoản",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (rl == null || rl.Count() == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in rl)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.DateTime),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.AccountNum),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return table;
        }
        protected void Button100Premium_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            Panel.Controls.Clear();
            Panel.Controls.Add(get100PremiumList(dateTimeList));
        }

        public Table get100PremiumList(List<DateTime> dateTimeList)
        {
            List<string> account100Charge = new List<string>();
            List<CharBase> list = new List<CharBase>();
            using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
            {
                foreach (Server server in serverList)
                {

                    string cmdText = "SELECT charid,level  FROM " + "charbase" + server.tablezone;
                    try
                    {
                        RowSet rowSet = session.Execute(cmdText);
                        foreach (Row r in rowSet)
                        {
                            CharBase cb = new CharBase();
                            cb.CharId = r["charid"].ToString();
                            cb.Level = r["level"].ToString();
                            list.Add(cb);
                        }
                    }
                    catch (Exception ex)
                    { }

                }
            }
            List<string> accountList = new List<string>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {

                string commandText = "Select account from filledcard";
                SqlCommand sqlCommand = new SqlCommand(commandText, conn);
                sqlCommand.CommandType = CommandType.Text;
                SqlDataReader datareader = sqlCommand.ExecuteReader();
                while (datareader.Read())
                {
                    accountList.Add(datareader.GetString(0));
                }
            }
            var query = (from tb in list where Int32.Parse(tb.Level) >= 100 select tb.CharId).ToList();
            foreach (string account in accountList)
            {
                foreach (Server server in serverList)
                {
                    string url = "http://{0}:{1}/getCharidName?gosuAccount={2}&game=38&zone={3}";
                    url = string.Format(url, hostName, port, account, server.gamezone);
                    string result = HttpHelper.HttpSocket(url, 26);
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    try
                    {
                        CharidName cin = jss.Deserialize<CharidName>(result.Substring(2, result.IndexOf("}", result.IndexOf("name")) - 1));
                        var hasCharge = query.Where(w => w.Equals(cin.charid)).FirstOrDefault();
                        if (hasCharge != null)
                        {
                            account100Charge.Add(account);
                        }
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            account100Charge = account100Charge.Distinct().ToList();
            //return account100Charge;

            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Account",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (account100Charge == null || account100Charge.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in account100Charge)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr),HorizontalAlign.Center,css),
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return table;
        }

        protected void ButtonImportExcel_Click(object sender, EventArgs e)
        {
            string pathSaveFile = ConfigurationManager.AppSettings["pathSaveFile"].ToString() + "\\" + FileUploadExcelFileChoose.FileName;
            if (FileUploadExcelFileChoose.HasFile)
            {
                FileUploadExcelFileChoose.SaveAs(pathSaveFile);
            }

            string sexcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathSaveFile + ";Extended Properties='Excel 12.0;IMEX=1;'";
            OleDbConnection oledbConn = new OleDbConnection(sexcelConnectionString);
            oledbConn.Open();

            string myExcelDataQuery = "select * from [{0}$]";
            myExcelDataQuery = string.Format(myExcelDataQuery, TextBoxSheetName.Text);
            OleDbCommand oledbCmd = new OleDbCommand(myExcelDataQuery, oledbConn);

            OleDbDataReader dr = oledbCmd.ExecuteReader();
            using (SqlConnection sqlcon = ConnectionHelper.GetConnection())
            {
                SqlTransaction sqlTran = sqlcon.BeginTransaction();
                while (dr.Read())
                {
                    //SqlCommand deleteCmd = new SqlCommand();
                    //deleteCmd.Connection = sqlcon;
                    //deleteCmd.Transaction = sqlTran;
                    //string deleteCmdText = "delete pitem where iditem =" + dr.GetString(0).Trim();
                    //deleteCmd.CommandText = deleteCmdText;
                    //int deleteAffectedRow = deleteCmd.ExecuteNonQuery();

                    SqlCommand insertCmd = new SqlCommand();
                    insertCmd.Connection = sqlcon;
                    insertCmd.Transaction = sqlTran;
                    string cmdText = "insert into pitem(iditem,nameitem,pricegolditem) values('{0}',N'{1}','{2}')";
                    cmdText = string.Format(cmdText, dr.GetValue(0).ToString().Trim(), dr.GetValue(1).ToString().Trim(), dr.GetValue(2).ToString().Trim());
                    insertCmd.CommandText = cmdText;
                    int affectedRow = insertCmd.ExecuteNonQuery();
                    if (affectedRow <= 0)
                    {
                        sqlTran.Rollback();
                        LabelMessage.Text = "Có một dòng không được import";
                        return;
                    }
                }
                sqlTran.Commit();
            }
            oledbConn.Close();
        }

        protected void ButtonOn100Rechard_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            Panel.Controls.Clear();
            Panel.Controls.Add(getOn100Rechard(dateTimeList));
        }

        private Table countGoodEvilCard(List<DateTime> dateTimeList)
        {
            List<GoodEvilCard> list = new List<GoodEvilCard>();
            foreach (var date in dateTimeList)
            {
                int num = 0;
                foreach (Server server in serverList)
                {
                    string commandText = "grep 'Thẻ Thiện Ác.*Mở Quà Thiện Ác nhận' /backup/log/{0}/objscene*.log.{1}*";
                    commandText = string.Format(commandText, server.serverphrasename, date.ToString("yyMMdd"));
                    SshClient client = ConnectionHelper.getSsh();
                    SshCommand command = client.CreateCommand(commandText, Encoding.UTF8);
                    command.CommandTimeout = TimeSpan.FromSeconds(3600);
                    client.SendKeepAlive();
                    command.Execute();
                    string result = command.Result;
                    byte[] byteArray = Encoding.UTF8.GetBytes(result);
                    MemoryStream stream = new MemoryStream(byteArray);
                    StreamReader streader = new StreamReader(stream);
                    num += result.Split('\n').Length - 1;
                    //while (true)
                    //{
                    //    string currentLine = streader.ReadLine();

                    //    if (string.IsNullOrEmpty(currentLine) == false)
                    //    {
                    //        num++;
                    //    }
                    //    else
                    //    {
                    //        break;
                    //    }
                    //}
                }
                GoodEvilCard gec = new GoodEvilCard();
                gec.datetime = date.ToString("dd/MM/yyyy");
                gec.cardNum = num;
                list.Add(gec);
            }
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Ngày",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Số thẻ",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (list == null || list.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in list)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.datetime),HorizontalAlign.Center,css),
                                     UIHelpers.CreateTableCell(Converter.ToString(dr.cardNum),HorizontalAlign.Center,css),
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return table;
        }

        protected void ButtonCountGoodEvilCard_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            Panel.Controls.Clear();
            Panel.Controls.Add(countGoodEvilCard(dateTimeList));
        }

        /// trừ ngày
        public static DateTime AddWorkdays(DateTime originalDate, int workDays)
        {
            DateTime tmpDate = originalDate;
            int a = workDays > 0 ? 1 : -1;
            while (workDays != 0)
            {
                tmpDate = tmpDate.AddDays(a);
                workDays -= a;
                //workDays--;
            }
            return tmpDate;
        }
        /// <summary>
        /// số tài khoản thất thoát tăng mới
        /// </summary>
        /// <returns></returns>
        public List<string> getAlluserlossnew(string dateTime, int server)
        {
            List<string> account = new List<string>();

            DateTime dday = Convert.ToDateTime(dateTime);
            DateTime beginday = AddWorkdays(dday, -6);
            DateTime begindayNAU = AddWorkdays(dday, -7);
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string where = server != 0 ? "au.gamezone=" + server + " and NAU.gamezone=" + server + " and" : "";
                    string whereNAU = server != 0 ? "gamezone=" + server + " and" : "";
                    string cmdText1 = "select DISTINCT(au.account)  from au join NAU on au.account = NAU.account where " + where + " adatetime between '" + beginday.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTime + " 23:59:59' and Cast(NAU.datetime as date) = '" + begindayNAU.ToString("yyyy-MM-dd") + "'";
                    string cmdText2 = "select DISTINCT(account) as account from nau where " + whereNAU + " datetime between '" + begindayNAU.ToString("yyyy-MM-dd") + " 00:00:00' AND '" + begindayNAU.ToString("yyyy-MM-dd") + " 23:59:59' ";
                    string cmdText3 = cmdText2 + " and account not in ( " + cmdText1 + " )";
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText3;
                    cmd.CommandType = CommandType.Text;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        account.Add(reader.GetValue(0).ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return account;
        }

        protected void ButtonAllAccountLossIncreNew_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            Panel.Controls.Clear();
            List<string> account = new List<string>();
            foreach (DateTime dt in dateTimeList)
            {
                //int fsaf= WebDB_N.getAlluserlossnew(dt.ToString("yyyy-MM-dd"), 0);
                account.AddRange(getAlluserlossnew(dt.ToString("yyyy-MM-dd"), 0).AsEnumerable());
            }
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Account",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (account == null || account.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in account)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonTESTTEMP_Click(object sender, EventArgs e)
        {
            // LossLevelStats lls = new LossLevelStats();
            //lls.getAccountAtTimeList(new DateTime(2015, 5, 12));
            //lls.getDataStats(new DateTime(2015, 5, 12));
            //int vn0304=WebDB_N.getLAU(new DateTime(2015, 5, 19).ToString("yyyy-MM-dd"), 0);
            //int vn04=WebDB_N.getLAU(new DateTime(2015, 5, 12).ToString("yyyy-MM-dd"), 1006);
            //string md51=CryptHelper.EncodeMD5("123456");
            //DateTime dt1 = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
            //DateTime dt2 = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Local);
            NauRechardWebDB nrWebDB = new NauRechardWebDB();
            nrWebDB.getNauRechar1Amount(new DateTime(2015, 5, 15));
        }

        protected void ButtonCountAccountAmountOfNauRechard_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            NauRechardWebDB nrWebDB = new NauRechardWebDB();
            List<NauRechardWebDB.NRAmount> list = new List<NauRechardWebDB.NRAmount>();
            foreach (DateTime date in dateTimeList)
            {
                list.AddRange(nrWebDB.getNauRechar1Amount(date));
            }
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Thời gian",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Account",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Amount(gosu)",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (list == null || list.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in list)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.DateTime.ToString("dd/MM/yyyy")),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.Account),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.Amount),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonCountA7UliemRechard_Click(object sender, EventArgs e)
        {
            DateTime date = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(txtA7UliemRechardDate.Text);
            List<Au> a7List = new List<Au>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand();
                string cmdText = "Select * from A7 where datetime between '{0}' and '{1}'";
                cmdText = string.Format(cmdText, date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                SqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    Au au = new Au();
                    au.Account = dataReader["account"].ToString().ToLower();
                    au.CharId = dataReader["charid"].ToString();
                    au.DateTime = dataReader.GetDateTime(0);
                    au.GameZone = dataReader["gamezone"].ToString();
                    a7List.Add(au);
                }
            }

            List<CharBase> cbList = new List<CharBase>();
            List<int> uliem = new List<int>() { 9, 10 };
            foreach (Server server in serverList)
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (int career in uliem)
                    {
                        string casCmdText = "SELECT charid  FROM " + "charbase" + server.tablezone + " where career=" + career;
                        try
                        {
                            RowSet rowSet = session.Execute(casCmdText);
                            foreach (Row r in rowSet)
                            {
                                CharBase cb = new CharBase();
                                cb.CharId = r["charid"].ToString();
                                //cb.Level = r["level"].ToString();
                                //cb.AccId = r["accid"].ToString();
                                cbList.Add(cb);
                            }
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }

            var accountHasCharUliem = (from a7 in a7List join cb in cbList on a7.CharId equals cb.CharId select new { account = a7.Account.ToLower() }).Distinct().ToList();

            List<FilledCard> fcList = new List<FilledCard>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand();
                string cmdText = "Select account,point from filledcard where filltime between '{0}' and '{1}'";
                cmdText = string.Format(cmdText, "2015-03-12" + " 00:00:00", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                SqlDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    FilledCard fc = new FilledCard();
                    fc.POINT = dataReader["point"].ToString();
                    fc.ACCOUNT = dataReader["account"].ToString().ToLower();
                    fc.GOSU = FilledCardWebDB.getRation(Convert.ToInt32(dataReader["point"].ToString())).ToString();
                    fcList.Add(fc);
                }
            }

            var result = (from tb in accountHasCharUliem join tb2 in fcList on tb.account equals tb2.ACCOUNT group tb2 by tb2.ACCOUNT into grp select new { Account = grp.Key, Amount = grp.Sum(s => int.Parse(s.GOSU)) }).ToList();

            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Account",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Amount(gosu)",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (result == null || result.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var dr in result)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.Account),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(dr.Amount),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonCheckActiveCode_Click(object sender, EventArgs e)
        {
            List<ActiveCode> codeList = new List<ActiveCode>();
            string pathSaveFile = ConfigurationManager.AppSettings["pathSaveFile"].ToString() + "\\" + FileUploadExcelFileChoose.FileName;
            if (FileUploadExcelFileChoose.HasFile)
            {
                try
                {
                    FileUploadExcelFileChoose.SaveAs(pathSaveFile);
                }
                catch (Exception ex)
                {

                }
            }

            string sexcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathSaveFile + ";Extended Properties='Excel 12.0;IMEX=1;'";
            OleDbConnection oledbConn = new OleDbConnection(sexcelConnectionString);
            oledbConn.Open();

            string myExcelDataQuery = "select * from [{0}$]";
            myExcelDataQuery = string.Format(myExcelDataQuery, TextBoxSheetName.Text);
            OleDbCommand oledbCmd = new OleDbCommand(myExcelDataQuery, oledbConn);

            OleDbDataReader dr = oledbCmd.ExecuteReader();
            while (dr.Read())
            {
                //using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                //{
                var charsToRemove = new string[] { "-" };
                string content = dr.GetValue(1).ToString();
                foreach (var c in charsToRemove)
                {
                    content = content.Replace(c, string.Empty);
                }
                if (string.IsNullOrEmpty(content) == false)
                {
                    string cmdText = "select usetime,useuid from pcard where gakey='{0}'";
                    cmdText = string.Format(cmdText, content.ToLower());
                    List<Row> rowList;
                    using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                    {
                        RowSet result = session.Execute(cmdText);
                        rowList = result.ToList();
                        session.Cluster.Shutdown();
                    }
           
                    if (rowList.Count > 0)
                    {
                        Row row = rowList[0];
                        string accid = row["useuid"].ToString();
                        string usetime = row.GetValue<DateTime>(0).ToLocalTime().AddHours(7).ToString();
                        string name = "";
                        foreach (Server server in serverList)
                        {
                            string cmdText2 = "select name from charbase" + server.tablezone + " where accid=" + accid;
                            List<Row> rowList2;
                            using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                            {
                                RowSet result2 = session.Execute(cmdText2);
                                rowList2 = result2.ToList();
                                session.Cluster.Shutdown();
                            }
                            if (rowList2.Count > 0)
                            {
                                Row row2 = rowList2[0];
                                name = row2["name"].ToString();
                                if (string.IsNullOrEmpty(name) == false)
                                {
                                    break;
                                }
                            }
                        }
                        codeList.Add(new ActiveCode() { Code = dr.GetValue(1).ToString(), UseTime = usetime, Name = name });
                    }
                }
                //Thread.Sleep(500);
            }
            //}
            dr.Close();
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Thời gian",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Code",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                     UIHelpers.CreateTableCell("Tên nhân vật",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (codeList == null || codeList.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var item in codeList)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.Code),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.UseTime),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.Name),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonCountCardHero_Click(object sender, EventArgs e)
        {
            //DateTime excutingDateTime = new DateTime(2000, 1, 1);
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);
            List<CardHero> list = new List<CardHero>();
            //foreach (Server s in serverList)
            //{
            foreach (var date in dateTimeList)
            {
                try
                {
                    // excutingDateTime = date;
                    string commandText = "grep 'Đổi tích điểm\\|Đổi Tiệm Lì Xì' /backup/log/222.255.177.{0}/objscene*.log.{1}*";
                    //string fileName = "trade{0}.txt";
                    // fileName = string.Format(fileName, date.ToString("yyMMdd"));
                    //string commandTextDeleteFile = "rm " + fileName;
                    commandText = string.Format(commandText, "*", date.ToString("yyMMdd"));
                    SshClient client = ConnectionHelper.getSsh();
                    //SshCommand deleteFileCommand = client.CreateCommand(commandTextDeleteFile, Encoding.UTF8);
                    //deleteFileCommand.CommandTimeout = TimeSpan.FromSeconds(50);
                    //deleteFileCommand.Execute();
                    SshCommand command = client.CreateCommand(commandText, Encoding.UTF8);
                    command.CommandTimeout = TimeSpan.FromSeconds(3600);
                    client.SendKeepAlive();
                    command.Execute();
                    string result = command.Result;
                    byte[] byteArray = Encoding.UTF8.GetBytes(result);
                    MemoryStream stream = new MemoryStream(byteArray);
                    StreamReader streader = new StreamReader(stream);

                    //SftpClient sftpClient = ConnectionHelper.getSftp();
                    //StreamReader streader = sftpClient.OpenText(fileName);

                    while (true)
                    {
                        string currentLine = streader.ReadLine();
                        //string wordToBeMatch1 = "buy_npc";
                        //string regex1 = string.Format(@"\b{0}\b", wordToBeMatch1);
                        DateTime dateTime = new DateTime();
                        string objId = "";
                        string objName = "";
                        string change = "";
                        string shop = "";
                        string characterName = "";
                        string action = "";
                        string content = "";
                        string baseid = "";
                        if (string.IsNullOrEmpty(currentLine) == false)
                        {
                            //if (Regex.IsMatch(currentLine, regex1))
                            //{
                            string time = currentLine.Substring(currentLine.IndexOf("-", currentLine.IndexOf("-") + 1) + 1, 8);
                            string dateStr = currentLine.Substring(currentLine.IndexOf(':') + 1, 6);
                            dateTime = DateTime.ParseExact(dateStr + " " + time, "yyMMdd HH:mm:ss", CultureInfo.InvariantCulture);
                            content = currentLine.Substring(currentLine.IndexOf("DEBUG:") + 7);
                            string[] contentArray = content.Split(',');

                            objName = contentArray[2];
                            change = contentArray[4];
                            shop = contentArray[7];
                            characterName = contentArray[9];
                            action = contentArray[10];
                            baseid = contentArray[11];
                            //}
                            CardHero dto = new CardHero();
                            dto.datetime = dateTime;
                            dto.objname = objName;
                            dto.baseid = baseid;
                            dto.change = change;
                            dto.shop = shop;
                            dto.characterName = characterName;
                            dto.action = action;
                            list.Add(dto);
                        }
                        else
                        {
                            break;
                        }

                    }
                }
                catch (Exception ex)
                {
                    //WebDB.WriteLog("", "", "Syns bsr" + tablezone + " data: " + ex.Message + ", ExecutingDateTime:" + excutingDateTime);
                }
            }
            //}
            var countList = list.GroupBy(g => (g.datetime + g.characterName)).Where(w => w.Count().Equals(2)).ToList();
            List<CardHero> chList = countList.SelectMany(s => s).ToList();
            //var fsafasfsaf = chList.Where(w => w.objname.Equals("Ngoại T.C (Bạch Hổ)") || w.objname.Equals("Cấp Anh Hùng Thiếp")).Where(w=>w.datetime.Equals(new DateTime(2015,6,20,14,6,32))).ToList();
            var endList = chList.Where(w => w.action.Equals("Đổi tích điểm") && (w.baseid.Equals("795") || w.baseid.Equals("3401") || w.baseid.Equals("407") || w.baseid.Equals("15759") || w.baseid.Equals("15760") || w.baseid.Equals("7101") || w.baseid.Equals("14479") || w.baseid.Equals("530") || w.baseid.Equals("792") || w.baseid.Equals("17570") || w.baseid.Equals("17575") || w.baseid.Equals("17580") || w.baseid.Equals("2910") || w.baseid.Equals("2911") || w.baseid.Equals("2912") || w.baseid.Equals("2631") || w.baseid.Equals("2635") || w.baseid.Equals("2639") || w.baseid.Equals("2643") || w.baseid.Equals("2647") || w.baseid.Equals("2651") || w.baseid.Equals("2655") || w.baseid.Equals("5000"))).ToList().GroupBy(g => g.objname).Select(s => new { s.Key, Amount = s.Sum(sum => int.Parse(sum.change)) }).ToList();
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Tên vật phẩm",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Số lượng",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (endList == null || endList.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var item in endList)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.Key),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.Amount),HorizontalAlign.Center,css),
                           }
                        );

                        table.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonCheckAgainGame_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);

            List<string> GameAgainLoginAccountList = new List<string>();
            string pathSaveFile = ConfigurationManager.AppSettings["pathSaveFile"].ToString() + "\\" + FileUploadAgainGame.FileName;
            if (FileUploadAgainGame.HasFile)
            {
                try
                {
                    FileUploadAgainGame.SaveAs(pathSaveFile);
                }
                catch (Exception ex)
                {

                }
            }

            string sexcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathSaveFile + ";Extended Properties='Excel 12.0;IMEX=1;'";
            OleDbConnection oledbConn = new OleDbConnection(sexcelConnectionString);
            oledbConn.Open();

            string myExcelDataQuery = "select * from [{0}$]";
            myExcelDataQuery = string.Format(myExcelDataQuery, TextBoxSheetAgainGame.Text);
            OleDbCommand oledbCmd = new OleDbCommand(myExcelDataQuery, oledbConn);

            OleDbDataReader dr = oledbCmd.ExecuteReader();
            while (dr.Read())
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.Text;
                    command.CommandText = "select account from au where adatetime between " + "'" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00'" + " and " + "'" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'" + " and account='" + dr["Username"].ToString() + "'";
                    SqlDataReader dataReader = command.ExecuteReader();
                    bool hasRow = dataReader.Read();
                    if (hasRow == true)
                    {
                        GameAgainLoginAccountList.Add(dr["Username"].ToString());
                    }
                }
            }
            dr.Close();
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Thời gian",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Code",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                     UIHelpers.CreateTableCell("Tên nhân vật",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (GameAgainLoginAccountList == null || GameAgainLoginAccountList.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var item in GameAgainLoginAccountList)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                    TableRow lastRow = new TableRow();
                    lastRow.Cells.AddRange
                    (
                        new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,"cell1"),
                                    UIHelpers.CreateTableCell(Converter.ToString(GameAgainLoginAccountList.Count),HorizontalAlign.Center,"cell1")
                           }
                    );

                    table.Rows.Add(lastRow);
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }

        protected void ButtonCheckAbadonAndFillCard_Click(object sender, EventArgs e)
        {
            DateTime dtFormDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.beginDateTextBox.Text);
            DateTime dtToDate = IDAdmin.Common.ConverterWorkData.convertStringToDateTime(CountToolBar.toDateTextBox.Text);
            List<DateTime> dateTimeList = IDAdmin.Common.ConverterWorkData.getDateListBetween(dtFormDate, dtToDate);

            List<AbadonFillCard> AbadonAndFillCardList = new List<AbadonFillCard>();
            string pathSaveFile = ConfigurationManager.AppSettings["pathSaveFile"].ToString() + "\\" + FileUploadAgainGame.FileName;
            if (FileUploadAgainGame.HasFile)
            {
                try
                {
                    FileUploadAgainGame.SaveAs(pathSaveFile);
                }
                catch (Exception ex)
                {

                }
            }

            string sexcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathSaveFile + ";Extended Properties='Excel 12.0;IMEX=1;'";
            OleDbConnection oledbConn = new OleDbConnection(sexcelConnectionString);
            oledbConn.Open();

            string myExcelDataQuery = "select * from [{0}$]";
            myExcelDataQuery = string.Format(myExcelDataQuery, TextBoxSheetAgainGame.Text);
            OleDbCommand oledbCmd = new OleDbCommand(myExcelDataQuery, oledbConn);

            OleDbDataReader dr = oledbCmd.ExecuteReader();
            while (dr.Read())
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.Text;
                    string cmdText = "select TOP 1 account from au where account='{0}' and adatetime > (select min(adatetime) from au where adatetime between '{1}' and '{2}' and account='{0}' ) and adatetime<=DATEADD(day,7,(select min(adatetime) from au where adatetime between '{1}' and '{2}' and account='{0}' ))";
                    cmdText = string.Format(cmdText, dr["ID"].ToString(), dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                    command.CommandText = cmdText;
                    Object checkAbadon = command.ExecuteScalar();

                    SqlCommand FCCommand = new SqlCommand();
                    FCCommand.Connection = conn;
                    FCCommand.CommandType = CommandType.Text;
                    string FCCommandText = "select sum(point) from filledcard where filltime between '{0}' and '{1}' and account='{2}'";
                    FCCommandText = string.Format(FCCommandText, dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00", DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59", dr["ID"].ToString());
                    FCCommand.CommandText = FCCommandText;
                    Object fillCard = FCCommand.ExecuteScalar();

                    AbadonFillCard AFD = new AbadonFillCard();
                    AFD.ID = dr["ID"].ToString();
                    if (checkAbadon == null)
                    {
                        AFD.Abadon = "X";
                    }
                    else
                    {
                        AFD.Abadon = "";
                    }
                    if (fillCard != null)
                    {
                        AFD.FillCard = fillCard.ToString();
                    }
                    AbadonAndFillCardList.Add(AFD);
                }
            }
            dr.Close();
            Table table = new Table();
            table.CssClass = "table1";
            table.CellSpacing = 1;
            TableRow rowHeader = new TableRow();
            rowHeader.Cells.AddRange
            (
                new TableCell[]
                {     
                    UIHelpers.CreateTableCell("STT",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("ID",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                    UIHelpers.CreateTableCell("Nạp thẻ",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader"),
                     UIHelpers.CreateTableCell("Rời bỏ",Unit.Percentage(5), HorizontalAlign.Center,"cellHeader")
                }
            );

            table.Rows.Add(rowHeader);
            try
            {
                if (AbadonAndFillCardList == null || AbadonAndFillCardList.Count == 0)
                {
                    TableRow rowEmpty = new TableRow();
                    rowEmpty.Cells.Add(UIHelpers.CreateTableCell("<p>Không có dữ liệu!</p>", HorizontalAlign.Center, "cell1", 4));
                    table.Rows.Add(rowEmpty);
                }
                else
                {
                    //labelCountOfUser.Text = string.Format("{0:N0}", dt.Rows.Count);
                    string css;
                    int stt = 0;
                    foreach (var item in AbadonAndFillCardList)
                    {
                        stt += 1;
                        css = stt % 2 == 0 ? "cell1" : "cell2";
                        TableRow row = new TableRow();
                        row.Cells.AddRange
                        (
                            new TableCell[]
                           {
                                    UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.ID),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.FillCard),HorizontalAlign.Center,css),
                                    UIHelpers.CreateTableCell(Converter.ToString(item.Abadon),HorizontalAlign.Center,css)
                           }
                        );

                        table.Rows.Add(row);
                    }
                    //TableRow lastRow = new TableRow();
                    //lastRow.Cells.AddRange
                    //(
                    //new TableCell[]
                    //{
                    //UIHelpers.CreateTableCell(stt.ToString(),HorizontalAlign.Center,"cell1"),
                    //UIHelpers.CreateTableCell(Converter.ToString(AbadonAndFillCardList.Count),HorizontalAlign.Center,"cell1")
                    //}
                    //);

                    //table.Rows.Add(lastRow);
                }
            }
            catch (Exception ex)
            {
            }
            Panel.Controls.Clear();
            Panel.Controls.Add(table);
        }//
    }
}